import requests
import time
ID = "ww7b4086d88a144eec"
SECRET = "Yy-hdts7KZHFDO8tzwWTNUfPiGyssUOo8XAPz2IOnbA"
url_get_access_token = "https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=" + ID + "&corpsecret=" + SECRET
print(url_get_access_token)

#获取access_token#
def get_access_token(ID,SECRET):
    access_token_json = requests.get(url_get_access_token).json()
    while access_token_json['errcode'] != 0:
        get_access_token(ID, SECRET)
        time.sleep(2)
    access_token = access_token_json['access_token']
    return access_token

get_access_token(ID,SECRET)

#获取通讯录成员列表#
url_get_member_list = "https://qyapi.weixin.qq.com/cgi-bin/user/simplelist?access_token=" + get_access_token(ID,SECRET) + "&department_id=1&fetch_child=1"
print(url_get_member_list)

from DBUtils.PooledDB import PooledDB
import pymysql

pool = PooledDB(pymysql, 10, host='localhost', user='root', passwd='zcxvbmn', db='weixindb', port=3306)
db = pool.connection()
cursor = db.cursor()

def clean_member_list():
    clean_member_list_sql = "DELETE FROM member_list_threading"
    cursor.execute(clean_member_list_sql)
    db.commit()  # 清空数据库
    return

def get_member_list():
    member_list = requests.get(url_get_member_list).json()['userlist']
    for member in member_list:
        member["userid"] = repr(member["userid"])
        member["department"] = member["department"][0] #随机取一个部门
        member["name"] = repr(member["name"])
        #获取所在部门名称
        get_department_name_sql = "select name from department_list where id=%d" % (member["department"])
        cursor.execute(get_department_name_sql)
        department_name = cursor.fetchone()[0]
        department_id = member["department"]
        while department_id != 1:
            get_parentid_sql = "select parentid from department_list where id=%d" % (department_id)
            cursor.execute(get_parentid_sql)
            parentid = cursor.fetchone()[0]
            get_parent_department_name_sql = "select name from department_list where id=%d" % (parentid)
            cursor.execute(get_parent_department_name_sql)
            parent_department_name = cursor.fetchone()[0]
            department_name = parent_department_name + "/" + department_name
            department_id = parentid
        member["department_name"] = repr(department_name)
        '''
        # 获取员工岗位
        url_get_member_detail = "https://qyapi.weixin.qq.com/cgi-bin/user/get?access_token=" + get_access_token(ID, SECRET) + "&userid=" + eval(member["userid"])
        member_detail = requests.get(url_get_member_detail).json()
        member["position"] = repr(member_detail["position"])
        url_get_client_list = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/list?access_token=" + get_access_token(ID, SECRET) + "&userid=" + eval(member["userid"])
        client_list = requests.get(url_get_client_list).json()['external_userid']
        member["client_count"] = len(client_list)  # 获取员工顾客总数#
        #下面注释掉的是获取企微顾客数#
        for client in client_list:
            i = 1  # i为计数器
            next_cursor = ''
            while i == 1 or next_cursor != '':
            url_get_client_detail = "https://qyapi.weixin.qq.com/cgi-bin/externalcontact/get?access_token=" + get_access_token(ID, SECRET) + "&external_userid=" + client
        '''
        cols = ", ".join('{}'.format(k) for k in member.keys())
        val_cols = ', '.join('{}'.format(k) for k in member.values())
        insert_member_list_sql = "insert into member_list_threading(%s) values(%s)" % (cols, val_cols)
        try:
            cursor.execute(insert_member_list_sql)
            db.commit()
        except Exception as e:
            print(e)
    return


def update_member_position():
    fetch_all_sql = "select * from member_list_threading"
    cursor.execute(fetch_all_sql)
    member_list_mysql_table = cursor.fetchall()
    for row in member_list_mysql_table:
        userid = row[1]
        url_get_member_detail = "https://qyapi.weixin.qq.com/cgi-bin/user/get?access_token=" + get_access_token(ID, SECRET) + "&userid=" + userid
        member_detail = requests.get(url_get_member_detail).json()
        position = repr(member_detail["position"])
        userid = repr(userid)
        update_member_position_sql = "update member_list_threading set position=%s where userid=%s" % (position, userid)
        try:
            cursor.execute(update_member_position_sql)
            db.commit()
        except:
            print(update_member_position_sql+"有问题")

#clean_member_list()
#get_member_list()
update_member_position()

